"""数据库操作工具"""

import sqlite3
import json
from pathlib import Path
from typing import List, Dict, Any, Optional, Union
from datetime import datetime
import threading
from contextlib import contextmanager

from ..models.workspace import Workspace
from ..models.file_info import FileInfo
from ..models.directory_rule import DirectoryRule


class Database:
    """SQLite数据库管理类"""
    
    def __init__(self, db_path: Union[str, Path]):
        """初始化数据库连接"""
        self.db_path = Path(db_path).expanduser()
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        self._lock = threading.Lock()
        self._init_database()
    
    def _init_database(self) -> None:
        """初始化数据库表结构"""
        with self.get_connection() as conn:
            # 工作空间表
            conn.execute("""
                CREATE TABLE IF NOT EXISTS workspaces (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    root_path TEXT NOT NULL,
                    created_at TIMESTAMP,
                    last_accessed TIMESTAMP,
                    is_active BOOLEAN,
                    total_directories INTEGER,
                    total_files INTEGER,
                    metadata TEXT
                )
            """)
            
            # 目录索引表
            conn.execute("""
                CREATE TABLE IF NOT EXISTS directory_index (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    workspace_id TEXT,
                    path TEXT NOT NULL,
                    parent_path TEXT,
                    level INTEGER,
                    keywords TEXT,
                    access_count INTEGER DEFAULT 0,
                    created_at TIMESTAMP,
                    FOREIGN KEY (workspace_id) REFERENCES workspaces (id)
                )
            """)
            
            # 文件操作历史表
            conn.execute("""
                CREATE TABLE IF NOT EXISTS file_operations (
                    id TEXT PRIMARY KEY,
                    workspace_id TEXT,
                    operation_type TEXT,
                    source_path TEXT,
                    target_path TEXT,
                    timestamp TIMESTAMP,
                    reversible BOOLEAN,
                    metadata TEXT,
                    FOREIGN KEY (workspace_id) REFERENCES workspaces (id)
                )
            """)
            
            # 用户偏好表
            conn.execute("""
                CREATE TABLE IF NOT EXISTS user_preferences (
                    workspace_id TEXT,
                    preference_key TEXT,
                    preference_value TEXT,
                    updated_at TIMESTAMP,
                    PRIMARY KEY (workspace_id, preference_key),
                    FOREIGN KEY (workspace_id) REFERENCES workspaces (id)
                )
            """)
            
            # 分析缓存表
            conn.execute("""
                CREATE TABLE IF NOT EXISTS analysis_cache (
                    file_hash TEXT PRIMARY KEY,
                    file_name TEXT,
                    analysis_result TEXT,
                    confidence REAL,
                    created_at TIMESTAMP,
                    expires_at TIMESTAMP
                )
            """)
            
            # 创建索引
            conn.execute("CREATE INDEX IF NOT EXISTS idx_directory_workspace ON directory_index(workspace_id)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_directory_path ON directory_index(path)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_operations_workspace ON file_operations(workspace_id)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_cache_expires ON analysis_cache(expires_at)")
            
            conn.commit()
    
    @contextmanager
    def get_connection(self):
        """获取数据库连接"""
        with self._lock:
            conn = sqlite3.connect(str(self.db_path), timeout=30.0)
            conn.row_factory = sqlite3.Row
            try:
                yield conn
            finally:
                conn.close()
    
    # 工作空间操作
    def save_workspace(self, workspace: Workspace) -> None:
        """保存工作空间"""
        with self.get_connection() as conn:
            conn.execute("""
                INSERT OR REPLACE INTO workspaces 
                (id, name, root_path, created_at, last_accessed, is_active, 
                 total_directories, total_files, metadata)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                workspace.id,
                workspace.name,
                str(workspace.root_path),
                workspace.created_at.isoformat(),
                workspace.last_accessed.isoformat() if workspace.last_accessed else None,
                workspace.is_active,
                workspace.total_directories,
                workspace.total_files,
                json.dumps(workspace.metadata)
            ))
            conn.commit()
    
    def get_workspace(self, workspace_id: str) -> Optional[Workspace]:
        """获取工作空间"""
        with self.get_connection() as conn:
            row = conn.execute(
                "SELECT * FROM workspaces WHERE id = ?", 
                (workspace_id,)
            ).fetchone()
            
            if row:
                return Workspace(
                    id=row['id'],
                    name=row['name'],
                    root_path=Path(row['root_path']),
                    created_at=datetime.fromisoformat(row['created_at']),
                    last_accessed=datetime.fromisoformat(row['last_accessed']) if row['last_accessed'] else None,
                    is_active=row['is_active'],
                    total_directories=row['total_directories'],
                    total_files=row['total_files'],
                    metadata=json.loads(row['metadata']) if row['metadata'] else {}
                )
            return None
    
    def list_workspaces(self, active_only: bool = True) -> List[Workspace]:
        """列出所有工作空间"""
        query = "SELECT * FROM workspaces"
        params = ()
        
        if active_only:
            query += " WHERE is_active = ?"
            params = (True,)
        
        query += " ORDER BY last_accessed DESC"
        
        workspaces = []
        with self.get_connection() as conn:
            for row in conn.execute(query, params):
                workspace = Workspace(
                    id=row['id'],
                    name=row['name'],
                    root_path=Path(row['root_path']),
                    created_at=datetime.fromisoformat(row['created_at']),
                    last_accessed=datetime.fromisoformat(row['last_accessed']) if row['last_accessed'] else None,
                    is_active=row['is_active'],
                    total_directories=row['total_directories'],
                    total_files=row['total_files'],
                    metadata=json.loads(row['metadata']) if row['metadata'] else {}
                )
                workspaces.append(workspace)
        
        return workspaces
    
    def delete_workspace(self, workspace_id: str) -> None:
        """删除工作空间及相关数据"""
        with self.get_connection() as conn:
            # 删除相关数据
            conn.execute("DELETE FROM user_preferences WHERE workspace_id = ?", (workspace_id,))
            conn.execute("DELETE FROM file_operations WHERE workspace_id = ?", (workspace_id,))
            conn.execute("DELETE FROM directory_index WHERE workspace_id = ?", (workspace_id,))
            conn.execute("DELETE FROM workspaces WHERE id = ?", (workspace_id,))
            conn.commit()
    
    # 目录索引操作
    def save_directory_index(self, workspace_id: str, directories: List[Dict[str, Any]]) -> None:
        """保存目录索引"""
        with self.get_connection() as conn:
            # 清除旧索引
            conn.execute("DELETE FROM directory_index WHERE workspace_id = ?", (workspace_id,))
            
            # 插入新索引
            for directory in directories:
                conn.execute("""
                    INSERT INTO directory_index 
                    (workspace_id, path, parent_path, level, keywords, created_at)
                    VALUES (?, ?, ?, ?, ?, ?)
                """, (
                    workspace_id,
                    directory['path'],
                    directory.get('parent_path'),
                    directory.get('level', 0),
                    json.dumps(directory.get('keywords', [])),
                    datetime.now().isoformat()
                ))
            conn.commit()
    
    def get_directory_structure(self, workspace_id: str) -> List[str]:
        """获取目录结构"""
        with self.get_connection() as conn:
            rows = conn.execute("""
                SELECT path FROM directory_index 
                WHERE workspace_id = ? 
                ORDER BY level, path
            """, (workspace_id,)).fetchall()
            
            return [row['path'] for row in rows]
    
    # 文件操作历史
    def save_file_operation(self, operation: Dict[str, Any]) -> None:
        """保存文件操作记录"""
        with self.get_connection() as conn:
            conn.execute("""
                INSERT INTO file_operations 
                (id, workspace_id, operation_type, source_path, target_path, 
                 timestamp, reversible, metadata)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                operation['id'],
                operation['workspace_id'],
                operation['operation_type'],
                operation['source_path'],
                operation['target_path'],
                operation['timestamp'],
                operation['reversible'],
                json.dumps(operation.get('metadata', {}))
            ))
            conn.commit()
    
    def get_file_operations(self, workspace_id: str, limit: int = 100) -> List[Dict[str, Any]]:
        """获取文件操作历史"""
        operations = []
        with self.get_connection() as conn:
            rows = conn.execute("""
                SELECT * FROM file_operations 
                WHERE workspace_id = ? 
                ORDER BY timestamp DESC 
                LIMIT ?
            """, (workspace_id, limit)).fetchall()
            
            for row in rows:
                operations.append({
                    'id': row['id'],
                    'workspace_id': row['workspace_id'],
                    'operation_type': row['operation_type'],
                    'source_path': row['source_path'],
                    'target_path': row['target_path'],
                    'timestamp': row['timestamp'],
                    'reversible': row['reversible'],
                    'metadata': json.loads(row['metadata']) if row['metadata'] else {}
                })
        
        return operations
    
    # 分析缓存操作
    def save_analysis_cache(self, file_hash: str, file_name: str, 
                          result: Dict[str, Any], ttl: int = 3600) -> None:
        """保存分析缓存"""
        expires_at = datetime.now().timestamp() + ttl
        
        with self.get_connection() as conn:
            conn.execute("""
                INSERT OR REPLACE INTO analysis_cache 
                (file_hash, file_name, analysis_result, confidence, created_at, expires_at)
                VALUES (?, ?, ?, ?, ?, ?)
            """, (
                file_hash,
                file_name,
                json.dumps(result),
                result.get('confidence', 0.0),
                datetime.now().isoformat(),
                datetime.fromtimestamp(expires_at).isoformat()
            ))
            conn.commit()
    
    def get_analysis_cache(self, file_hash: str) -> Optional[Dict[str, Any]]:
        """获取分析缓存"""
        with self.get_connection() as conn:
            row = conn.execute("""
                SELECT analysis_result FROM analysis_cache 
                WHERE file_hash = ? AND expires_at > ?
            """, (file_hash, datetime.now().isoformat())).fetchone()
            
            if row:
                return json.loads(row['analysis_result'])
            return None
    
    def cleanup_expired_cache(self) -> None:
        """清理过期缓存"""
        with self.get_connection() as conn:
            conn.execute("""
                DELETE FROM analysis_cache 
                WHERE expires_at < ?
            """, (datetime.now().isoformat(),))
            conn.commit()